﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using MiniExcelLibs;
using System.Threading.Tasks;
using System.Drawing;
using System.Data;
using IntoStationSystem.Models;
using S7.Net.Types;
using System.Reflection;
using IntoStationSystem.Entity;
using Newtonsoft.Json.Linq;
using System.Runtime.InteropServices;
using System.Data.Entity.Core.Metadata.Edm;

namespace IntoStationSystem.Db
{
    public class ExcelLibClass
    {
        /// <summary>
        /// 获取表格中某一列的数据
        /// </summary>
        /// <param name="excelFilePath">要查询的文件路径</param>
        /// <param name="columnIndex">要查询的列</param>
        /// <returns></returns>
        /// <exception cref="FileNotFoundException"></exception>
        public List<string> GetColumnData(string excelFilePath, string ColumnName, string SheetName)
        {
            // 确保文件存在
            if (!File.Exists(excelFilePath))
                throw new FileNotFoundException("文件未找到", excelFilePath);
            // 读取 Excel 文件到 DataTable
            DataTable dataTable = MiniExcel.QueryAsDataTable(excelFilePath);
            List<string> columns = dataTable.AsEnumerable().Select(row => row.Field<string>(ColumnName)).ToList();
            return columns;
        }

        /// <summary>
        /// 将DataTable写入表格
        /// </summary>
        /// <param name="Path"></param>
        /// <param name="ExcelGrid"></param>
        /// <returns></returns>
        public bool WirtieDataTableExcel(string Path, DataTable ExcelGrid, string SheetName)
        {
          
            FileInfo file = new FileInfo(GetPath(Path));
            bool result = false;
            if (file.Exists)
            {
                file.Delete();
                var rows = ExcelGrid;
                try
                {
                    MiniExcel.SaveAsAsync(Path, rows, true, sheetName: SheetName);
                    result = true;
                }
                catch (Exception)
                {
                    result = false;
                }
            }
            return result;
        }
        /// <summary>
        /// 导出表格到指定的路径
        /// </summary>
        /// <param name="Path">指定的路径</param>
        /// <param name="ExcelGrid">Datetable的名称</param>
        /// <returns>返回结果</returns>
        public bool SaveDateTable(string Path, DataTable ExcelGrid, string SheetName)
        {
            FileInfo file = new FileInfo(Path);
            bool result = false;
            if (!file.Exists)
            {
                var rows = ExcelGrid;
                try
                {
                    MiniExcel.SaveAsAsync(Path, rows, true, sheetName: SheetName);
                    result = true;
                }
                catch (Exception)
                {
                    result = false;
                }
            }
            return result;
        }


        /// <summary>
        /// 将表格数据转换成二维数组
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public string[,] ReadExcelName(string path, string sheetName)
        {
#pragma warning disable CS0618 // 类型或成员已过时
            var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true, sheetName);
#pragma warning restore CS0618 // 类型或成员已过时
            DataTable Mytable = table as DataTable;
            string[,] Result = new string[Mytable.Rows.Count, Mytable.Columns.Count];
            for (int i = 0; i < Mytable.Rows.Count; i++)
            {
                for (int j = 0; j < Mytable.Columns.Count; j++)
                {
                    Result[i, j] = Mytable.Rows[i][j].ToString();
                }
            }
            return Result;
        }
        public string[,] ReadExcelCell(string path, string sheetName)
        {
#pragma warning disable CS0618 // 类型或成员已过时
            var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true, sheetName);
#pragma warning restore CS0618 // 类型或成员已过时
            DataTable Mytable = table as DataTable;
            string[,] Result = new string[Mytable.Rows.Count, Mytable.Columns.Count];
            for (int i = 0; i < Mytable.Columns.Count; i++)
            {
                for (int j = 0; j < Mytable.Rows.Count; j++)
                {
                    Result[i, j] = Mytable.Rows[i][j].ToString();
                }
            }
            return Result;
        }
        /// <summary>
        /// 查询一个Excel表格
        /// </summary>
        /// <param name="path">表格的路径</param>
        /// <param name="sheetName">表格的名字</param>
        /// <returns>返回一个表格的DataTable</returns>
        public DataTable ExcelTable(string path, string sheetName)
        {
            var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true, sheetName);
            DataTable Exceltable = table as DataTable;
            if (Exceltable != null)
            {
                return Exceltable;
            }
            return Exceltable;
        }
        /// <summary>
        /// 读取表格的输入输出数据
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static List<IO> ReadExcelLedName(string path, string sheetName,bool UseHeader)
        {            
            // 获取当前工作目录
            string currentDirectory = Directory.GetCurrentDirectory();
            // 结合相对路径和当前工作目录以获得绝对路径
            string absolutePath = Path.Combine(currentDirectory, path);
#pragma warning disable CS0618 // 类型或成员已过时
            var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: UseHeader, sheetName);                     
#pragma warning restore CS0618 // 类型或成员已过时
            DataTable Mytable = table as DataTable;
            IO[] iOState = new IO[Mytable.Rows.Count];
           
            for (int i = 0; i < Mytable.Rows.Count; i++)
            {
                for (int j = 0; j < Mytable.Columns.Count; j++)
                {
                    iOState[i]=new IO();
                    iOState[i].Addr = Mytable.Rows[i][2].ToString();
                    iOState[i].Desc = Mytable.Rows[i][1].ToString();
                }
            }
           return iOState.ToList();
        }
        /// <summary>
        /// 获取表格最大页数
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public int SheetCount(string path)
        {
            var sheetNames = MiniExcel.GetSheetNames(path);
            int sheetCount = sheetNames.Count;
            return sheetCount;
        }
        private string GetPath(string path)
        {
            // 获取当前工作目录
            string currentDirectory = Directory.GetCurrentDirectory();
            // 结合相对路径和当前工作目录以获得绝对路径
           return Path.Combine(currentDirectory, path);
        }
    }
}
